﻿Option Explicit On
Option Strict On

''' <summary>
''' Formulario consultar citas: Para consultar las citas existentes en la BBDD según el rango
''' especificado
''' </summary>
''' <author>
''' David Martínez
''' Julio Antoranz
''' </author>

Public Class ConsultaCitas
    Private _database As BBDD
    Private numeroSemana As Integer
    Private mes As Integer
    Private anyo As Integer

    Private Sub ActualizarTablaSemana()
        'Borramos todas las filas antes de recargar
        dgvTabla.Rows.Clear()

        Dim listaCitas As List(Of Cita)
        Dim client As Cliente
        Dim diaComienzo As Integer

        diaComienzo = DateTime.UtcNow.DayOfWeek

        If diaComienzo = 0 Then
            diaComienzo = 7
        End If

        Dim contador As Integer
        Dim celdaHora As Integer
        Dim contadorFilas As Integer
        Dim emple As Usuario
        Dim service As List(Of Servicio)
        Dim precioTotal As Single
        Dim conceptos As String

        Try

            'Conectamos a la BBDD
            _database.Conectar()

            ' obtenemos las citas
            listaCitas = _database.ListaCitas("SELECT * from cita where fecha >= #" & DateTime.UtcNow.AddDays(numeroSemana + (diaComienzo - 1) * -1).ToString("MM/dd/yyyy") & "# " & _
                                              " and fecha < #" & DateTime.UtcNow.AddDays(numeroSemana + (7 - diaComienzo) + 1).ToString("MM/dd/yyyy") & "# " & _
                                              "order by fecha")

            contadorFilas = 0

            For Each cita As Cita In listaCitas
                diaComienzo = cita.Fecha.DayOfWeek
                celdaHora = -1

                If diaComienzo = 0 Then
                    diaComienzo = 7
                End If

                contador = 0

                While contador < dgvTabla.Rows.Count
                    If dgvTabla.Rows(contador).Cells(0).Value.ToString = cita.Fecha.ToString("HH:mm") Then
                        celdaHora = contador
                    End If
                    contador += 1
                End While


                ' obtenemos los datos de la cita
                client = _database.DevuelveCliente("Select * from cliente where cod = " & CStr(cita.CodigoCliente) & ";")
                emple = _database.DevuelveUsuario("Select * from empleado where cod = " & CStr(cita.CodigoUsuario))
                service = _database.ListaServicios("Select * from servicios where cod in (Select cod_servicio from concepto where cod_cita = " & CStr(cita.Codigo) & ")")

                conceptos = ""
                precioTotal = 0

                For Each ServicioUnico As Servicio In service
                    conceptos = conceptos + ServicioUnico.Nombre + ","
                    precioTotal += ServicioUnico.Precio
                Next

                If celdaHora > 0 Then
                    dgvTabla.Rows(celdaHora).Cells(diaComienzo).Value = client.Nombre
                Else
                    dgvTabla.Rows.Add()
                    dgvTabla.Rows(contadorFilas).Cells(diaComienzo).Value = "Cliente: " & client.Nombre & vbCrLf & "Empleado: " & emple.Nombre & vbCrLf & "Concepto: " & conceptos & vbCrLf & "Precio: " & precioTotal
                    dgvTabla.Rows(contadorFilas).Cells(0).Value = cita.Fecha.ToString("HH:mm")
                    contadorFilas += 1
                End If

            Next

            'Cerramos la BBDD
            _database.Desconectar()
            dgvTabla.Sort(dgvTabla.Columns(0), System.ComponentModel.ListSortDirection.Ascending)
        Catch ex As Exception
            MsgBox("Hubo un error al conectar la base de datos: " & ex.Message)

        End Try

    End Sub

    Private Sub ActualizarTablaMes()
        'Borramos todas las filas antes de recargar
        dgvTabla.Rows.Clear()

        Dim listaCitas As List(Of Cita)
        Dim client As Cliente
        Dim service As List(Of Servicio)
        Dim precioTotal As Single
        Dim conceptos As String

        Try
            'Conectamos a la BBDD
            _database.Conectar()

            ' obtenemos las citas
            listaCitas = _database.ListaCitas("SELECT * from cita where fecha >= #" & DateTime.UtcNow.AddMonths(mes).ToString("MM/01/yyyy") & "# " & _
                                              " and fecha <= #" & DateTime.UtcNow.AddMonths(mes + 1).AddDays(-DateTime.Now.Day).ToString("MM/dd/yyyy") & "# " & _
                                              "order by fecha")

            For Each cita As Cita In listaCitas
                conceptos = ""
                precioTotal = 0

                client = _database.DevuelveCliente("Select * from cliente where cod = " & CStr(cita.CodigoCliente) & ";")
                service = _database.ListaServicios("Select * from servicios where cod in (Select cod_servicio from concepto where cod_cita = " & CStr(cita.Codigo) & ")")

                For Each ServicioUnico As Servicio In service
                    conceptos = conceptos + ServicioUnico.Nombre + ","
                    precioTotal += ServicioUnico.Precio
                Next

                dgvMes.Rows.Add(cita.Fecha.ToString("dd/MM"), cita.Fecha.ToString("hh:mm"), client.Nombre, conceptos, precioTotal, cita.Recordatorio)
            Next

            'Cerramos la BBDD
            _database.Desconectar()
            dgvTabla.Sort(dgvTabla.Columns(0), System.ComponentModel.ListSortDirection.Ascending)
        Catch ex As Exception
            MsgBox("Hubo un error al conectar la base de datos: " & ex.Message)

        End Try

    End Sub

    Private Sub ActualizarTablaAnyo()
        'Borramos todas las filas antes de recargar
        dgvAnyos.Rows.Clear()

        Dim listaCitas As List(Of Cita)
        Dim client As Cliente
        Dim service As List(Of Servicio)
        Dim precioTotal As Single
        Dim conceptos As String

        Try
            'Conectamos a la BBDD
            _database.Conectar()

            ' obtenemos las citas
            listaCitas = _database.ListaCitas("SELECT * from cita where fecha >= #" & "01/01/" & anyo.ToString("0000") & "# " & _
                                              " and fecha <= #" & "12/31/" & anyo.ToString("0000") & "# " & _
                                              "order by fecha")

            For Each cita As Cita In listaCitas
                conceptos = ""
                precioTotal = 0

                client = _database.DevuelveCliente("Select * from cliente where cod = " & CStr(cita.CodigoCliente) & ";")
                service = _database.ListaServicios("Select * from servicios where cod in (Select cod_servicio from concepto where cod_cita = " & CStr(cita.Codigo) & ")")

                For Each ServicioUnico As Servicio In service
                    conceptos = conceptos + ServicioUnico.Nombre + ","
                    precioTotal += ServicioUnico.Precio
                Next

                dgvAnyos.Rows.Add(cita.Fecha.ToString("dd/MM"), cita.Fecha.ToString("hh:mm"), client.Nombre, conceptos, precioTotal, cita.Recordatorio)
            Next

            'Cerramos la BBDD
            _database.Desconectar()
        Catch ex As Exception
            MsgBox("Hubo un error al conectar la base de datos: " & ex.Message)

        End Try

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Database1DataSet.cita' table. You can move, or remove it, as needed.
        anyo = Date.UtcNow.Year
        Me.CitaTableAdapter.Fill(Me.Database1DataSet.cita)
        _database = New BBDD
        numeroSemana = 0
        mes = 0
        ActualizarTablaSemana()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        dgvMes.Visible = True
        dgvAnyos.Visible = False
        dgvTabla.Visible = False
        dgvMes.Rows.Clear()
        ActualizarTablaMes()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        dgvAnyos.Visible = True
        dgvMes.Visible = False
        dgvTabla.Visible = False
        dgvAnyos.Rows.Clear()
        ActualizarTablaAnyo()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dgvTabla.Rows.Clear()
        dgvTabla.Visible = True
        dgvMes.Visible = False
        dgvAnyos.Visible = False
        ActualizarTablaSemana()
    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        If dgvTabla.Visible = True Then
            numeroSemana -= 7
            dgvTabla.Rows.Clear()
            ActualizarTablaSemana()
        ElseIf dgvMes.Visible = True Then
            mes -= 1
            dgvMes.Rows.Clear()
            ActualizarTablaMes()
        Else
            anyo -= 1
            dgvAnyos.Rows.Clear()
            ActualizarTablaAnyo()
        End If
        
    End Sub

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        If dgvTabla.Visible = True Then
            numeroSemana += 7
            dgvTabla.Rows.Clear()
            ActualizarTablaSemana()
        ElseIf dgvMes.Visible = True Then
            mes += 1
            dgvMes.Rows.Clear()
            ActualizarTablaMes()
        Else
            anyo += 1
            dgvAnyos.Rows.Clear()
            ActualizarTablaAnyo()
        End If
    End Sub
End Class
